import java.sql.*;
public class ReportGenerator
{
	Connection connection;
	public ReportGenerator(Connection connection)
	{
		this.connection = connection;
	}
	public void reportOccByRoomType()
	{
		String query = "select category, count(*) from room where avail='F' group by category";
		try
		{
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
//			System.out.println("ResultSet fetch Size " + rs.getFetchSize());
			System.out.print("Occupancy by room type :"); 
			while(rs.next())
			{
				System.out.println(rs.getString(1) + " " + rs.getInt(2));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

	}
	public void reportOccByDateRange(String startdt, String enddt)
	{
	try
		{
			PreparedStatement st = connection.prepareStatement("select count(*) from room where avail='F' and (hid, rnumber) in (select r_hid, r_rnumber from reserves where r_checkindt >= to_timestamp(?,'YYYY-MM-DD HH:MI:SS') AND to_timestamp(?,'YYYY-MM-DD HH:MI:SS')<=r_checkoutdt)");
			st.setString(1, startdt);
			st.setString(2, enddt);
			ResultSet rs = st.executeQuery();
			System.out.println("Occupancy by date range between "+ startdt+ " and " +enddt+" :\n");
			while(rs.next())
			{
				System.out.println(rs.getInt(1));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
	}
	public void reportOccByHotel()
	{
		try
		{
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery("select hid, count(*) from room where avail='F' group by(hid)");
			System.out.println("Occupancy by hotel :\n");
			while(rs.next())
			{
				System.out.println(rs.getInt(1)+" "+rs.getInt(2));
//				System.out.println(rs.getInt(1)+" "+rs.getString(2) + " "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5)+" "+rs.getString(6)+" "+rs.getFloat(7)+" "+rs.getInt(8)+" "+rs.getInt(9));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	

	}
	public void reportOccupants(String startdt, String enddt)
	{
		try
		{
			PreparedStatement st = connection.prepareStatement("select cid, cname, cemail, caddr, cphone from customer where cid in (select cid from reservation where resid in (select r_resid from reserves where r_checkindt>=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r_checkindt<=to_timestamp(?,'YYYY-MM-DD HH:MI:SS')))");
			st.setString(1, startdt);
			st.setString(2, enddt);
			ResultSet rs = st.executeQuery();
			System.out.println("Occupants by date range between "+ startdt+ " and " +enddt+" :\n");
			while(rs.next())
			{
				System.out.println(rs.getInt(1)+" "+rs.getString(2)+ " "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	

	}
	public void reportPercentRoomsOccupied()
	{
		String query = "select sum(case when avail='T' then 1 else 0 end)*100/count(*) from room";
		try
		{
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
//			System.out.println("ResultSet fetch Size " + rs.getFetchSize());
			System.out.println("Percentage of Rooms Occupied :\n"); 
			while(rs.next())
			{
				System.out.println(rs.getFloat(1));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	public void reportStaffByRole(String jobtitle)
	{
		try
		{
			PreparedStatement st = connection.prepareStatement("select staffid, sname, semail, saddr, jobtitle, sphone, salary, hid, sage from staff where jobtitle=?");
			st.setString(1,jobtitle);
			ResultSet rs = st.executeQuery();
			System.out.println("Information on staff grouped by the role "+jobtitle+"\n");
			while(rs.next())
			{
				System.out.println(rs.getInt(1)+" "+rs.getString(2) + " "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5)+" "+rs.getString(6)+" "+rs.getFloat(7)+" "+rs.getInt(8)+" "+rs.getInt(9));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
	}
	public void reportCustomerByStaff(int staffid, String startdt, String enddt)
	{
		try
		{
			PreparedStatement st = connection.prepareStatement("select cid, cname, caddr, cemail, cphone from customer where cid in (select cid from reservation where resid in (select r_resid from reserves where (r_hid, r_rnumber) in ((select a_hid, a_rnumber from assigned a, reserves r where a.a_hid = r.r_hid and a.a_rnumber = r.r_rnumber and a.a_staffid=? and r.r_checkindt<=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r.r_checkoutdt>=to_timestamp(?,'YYYY-MM-DD HH:MI:SS')) union (select a_hid, a_rnumber from assigned a, reserves r where a.a_hid = r.r_hid and a.a_rnumber = r.r_rnumber and a.a_staffid=? and r.r_checkindt>=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r.r_checkoutdt<=to_timestamp(?,'YYYY-MM-DD HH:MI:SS')) union (select a_hid, a_rnumber from assigned a, reserves r where a.a_hid = r.r_hid and a.a_rnumber = r.r_rnumber and a.a_staffid=? and r.r_checkindt<=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r.r_checkoutdt<=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r.r_checkoutdt>=to_timestamp(?,'YYYY-MM-DD HH:MI:SS')) union (select a_hid, a_rnumber from assigned a, reserves r where a.a_hid = r.r_hid and a.a_rnumber = r.r_rnumber and a.a_staffid=? and r.r_checkindt>=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r.r_checkindt<=to_timestamp(?,'YYYY-MM-DD HH:MI:SS') and r.r_checkoutdt>=to_timestamp(?,'YYYY-MM-DD HH:MI:SS')))))");
			st.setInt(1, staffid);
			st.setString(2, startdt);
			st.setString(3, enddt);
			st.setInt(4, staffid);
			st.setString(5, startdt);
			st.setString(6, enddt);
			st.setInt(7, staffid);
			st.setString(8, startdt);
			st.setString(9, enddt);
			st.setString(10, startdt);
			st.setInt(11, staffid);
			st.setString(12, startdt);
			st.setString(13, enddt);
			st.setString(14, enddt);
			ResultSet rs = st.executeQuery();
			System.out.println("Information on customers alloted to staff ID " + staffid +" :\n");
			while(rs.next())
			{
				System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5));
			}
			System.out.println("\n\n");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
	}
}

